<?php
class LogModel extends BaseModel
{
    public static function truncate()
    {
        $sql = 'TRUNCATE log;';
        Yii::app()->db->createCommand($sql)->execute();
    }
    
    public static function import($file)
    {
        $sql = 'LOAD DATA LOCAL INFILE "%s"
                INTO TABLE log
                CHARACTER SET UTF8
                FIELDS TERMINATED BY "|" ENCLOSED BY "\""
                LINES TERMINATED BY "\n";';
        $sql = sprintf($sql, $file);
        Yii::app()->db->createCommand($sql)->execute();
    }
    
    public static function delivery()
    {
        // config
        $logs = require(Yii::getPathOfAlias('application.config.log').'.php');
        
        // sql
        $sql = '';
        foreach($logs as $type => $log)
        {
            // param
            $param = '';
            for($i = 1; $i <= $log[ENUM_LOG_TABLE_NUM_PARAM]; $i++)
            {
                $param .= sprintf('p%d, ', $i+10);
            }
            $param = substr($param, 0, -2);

            // add sql
            $sql .= sprintf
            (
                'INSERT INTO %s SELECT p1, p2, DATE(p2), %s FROM log WHERE p3 = %d;',
                $log[ENUM_LOG_TABLE_NAME],
                $param,
                $type
            );
        }

        // execute
        Yii::app()->db->createCommand($sql)->execute();
    }
    
    public static function delete($date)
    {
        // config
        $logs = require(Yii::getPathOfAlias('application.config.log').'.php');
        
        // sql
        $sql = '';
        foreach($logs as $log)
        {
            $sql .= sprintf('DELETE FROM %s WHERE DATE(created_at) = :date;', $log[ENUM_LOG_TABLE_NAME]);
        }

        // execute
        Yii::app()->db->createCommand($sql)->execute(array(':date' => $date));
    }
    
    public static function getGiftAmount($date)
    {
        $sql = 'SELECT
                    image,
                    COUNT(*) as amount
                FROM log_send_gift
                WHERE DATE(created_at) = :date
                GROUP BY image
                ORDER BY amount DESC
                LIMIT 10';

        return Yii::app()->db->createCommand($sql)->bindParam(':date', $date)->queryAll();
    }

    public static function getStickerAmount($date)
    {
        $sql = 'SELECT
                    image,
                    COUNT(*) as amount
                FROM log_send_sticker
                WHERE DATE(created_at) = :date
                GROUP BY image
                ORDER BY amount DESC
                LIMIT 10';

        return Yii::app()->db->createCommand($sql)->bindParam(':date', $date)->queryAll();
    }
}
